<?php

namespace mini;

use PDO;
use Exception;

class DB
{
    public static $operater;
    public $table;
    public $pdo;
    public $sql;
    public $pretreatment;
    public $where;
    public $groupBy;
    public $join;
    public $orderBy;
    public $limit;
    public $eachPage;
    public $totalRows;
    public $conf;

    public function __construct($conf)
    {
        $this->conf = $conf;

        try {
            switch ($this->conf['driver']) {
                case "sqlsrv":
                    $this->pdo  = new PDO("{$conf['driver']}:Server={$conf['host']},{$conf['port']};Database={$conf['database']}", $conf['username'], $conf['password']);
                    break;
                case "mysql":
                    $this->pdo  = new PDO("mysql:host={$conf['host']};port={$conf['port']};dbname={$conf['database']}", $conf['username'], $conf['password']);
                    $this->pdo->query("set names {$conf['charset']}");
                    $this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
                    $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
                    break;
                default:
                    throw new Exception('不支持的数据库驱动');
            }
        } catch (Exception $e) {
            throw new Exception('数据库连接失败, 请检查数据库相关配置');
        }
    }

    public function __clone()
    {
        $this->sql          = null;
        $this->pretreatment = null;
        $this->where        = null;
        $this->groupBy      = null;
        $this->join         = null;
        $this->orderBy      = null;
        $this->limit        = null;
        $this->eachPage     = null;
    }

    /**
     * 单例模式
     */
    public static function getInstance($conf, $table, $configName)
    {
        $table = $conf['prefix'] . $table;
        if (!isset(self::$operater[$configName])) {
            self::$operater[$configName] = new db($conf);
            self::$operater[$configName]->table = $table;
            return self::$operater[$configName];
        }
        if (self::$operater[$configName]->table == $table) {
            return self::$operater[$configName];
        }
        $cloner = clone self::$operater[$configName];
        $cloner->table = $table;
        return $cloner;
    }


    // 记录 sql 运行过程
    protected function trace($res, $startTime, $sql = null)
    {
        if ($sql === null) {
            $sql = $this->sql;
        }

        $sqlRec = [];
        $sqlRec[0] = $res != FALSE ? 'success' : 'false';
        $sqlRec[1] = $sql;
        $sqlRec[2] = round((microtime(true) - $startTime) * 1000, 2);
        $sqlRec[3] = $res ? '' : $this->error();
        $GLOBALS['traceSql'][] = $sqlRec;
    }

    // 执行 sql 语句
    public function query($sql, $execute = null)
    {
        $startTime = microtime(true);
        $this->pretreatment = $this->pdo->prepare($sql);
        $res = $this->pretreatment->execute($execute);
        $this->trace($res, $startTime, $sql);
        return $res;
    }

    // 获取来自 query 查询的单条数据
    public function queryFetch()
    {
        return $this->pretreatment->fetch(PDO::FETCH_OBJ);
    }

    // 获取来自 query 查询的全部数据
    public function queryFetchAll()
    {
        return $this->pretreatment->fetchAll(PDO::FETCH_OBJ);
    }

    /**
     * 插入数据
     *
     * 返回写入数据对应的主键数据值
     *
     * @param  array  $data  插入的数据
     * @return
     */
    public function insert(array $data = [])
    {
        $startTime = microtime(true);
        if (!is_array($data) || empty($data)) {
            throw new Exception('插入数据错误，插入数据应为非空的一维数组');
        }
        $this->sql   = "INSERT INTO $this->table (";
        $fields      = [];
        $placeholder = [];
        $insertData  = [];
        foreach ($data as $k => $v) {
            $fields[] = "$k";
            $placeholder[] = "?";
            $insertData[] = $v;
        }
        $this->sql .= implode(', ', $fields) . ') values (' . implode(', ', $placeholder) . ');';
        $this->pretreatment = $this->pdo->prepare($this->sql);
        $this->pretreatment->execute($insertData);
        $res = $this->pdo->lastInsertId();
        $this->trace($res, $startTime);
        return $res;
    }

    /**
     * 批量插入数据
     *
     * 返回最后一条写入数据对应的主键数据值
     *
     * @param  array  $dataList  批量插入的数据，二维数组
     * @return int
     */
    public function batchInsert(array $dataList = [])
    {
        $startTime = microtime(true);
        if (!is_array($dataList) || empty($dataList)) {
            throw new Exception('插入数据错误, 插入数据应为一个非空的二维数组');
        }
    
        // 获取字段名  
        $fields = array_keys($dataList[0]);
        $this->sql = "INSERT INTO $this->table (" . implode(', ', $fields) . ') VALUES ';

        $placeholder = [];
        $insertData = [];
        foreach ($dataList as $data) {
            $rowPlaceholder = [];
            foreach ($data as $value) {
                $rowPlaceholder[] = '?';
                $insertData[] = $value;
            }
            $placeholder[] = '(' . implode(', ', $rowPlaceholder) . ')';
        }

        $this->sql .= implode(', ', $placeholder) . ';';
        $this->pretreatment = $this->pdo->prepare($this->sql);
        $this->pretreatment->execute($insertData);
        $res = $this->pdo->lastInsertId();
        $this->trace($res, $startTime);
        return $res;
    }

    /**
     * 删除数据
     *
     * @return bool
     */
    public function delete()
    {
        $startTime = microtime(true);
        if (empty($this->where)) {
            throw new Exception('请使用模型对象的 where() 函数设置删除条件');
        }
        $where              = $this->getWhere();
        $this->sql          = "DELETE FROM $this->table {$where[0]};";
        $this->pretreatment = $this->pdo->prepare($this->sql);
        $res = $this->pretreatment->execute($where[1]);
        $this->trace($res, $startTime);
        return $res;
    }

    /**
     * 更新数据
     *
     * @param  array  $data  更新的数据
     * @return bool
     */
    public function update($data = null)
    {
        $startTime = microtime(true);
        if (empty($data) || !is_array($data)) {
            throw new Exception('update 的参数应该为一个一维数组');
        }
        if (empty($this->where)) {
            throw new Exception('请使用模型对象的 where() 方法设置更新条件');
        }
        $where = $this->getWhere();
        $this->sql   = "UPDATE {$this->table} SET ";
        $updateData  = [];
        foreach ($data as $k => $v) {
            $this->sql   .= "$k = ?, ";
            $updateData[] = $v;
        }
        $this->sql  = substr($this->sql, 0, -2) . $where[0] . ';';
        $updateData = array_merge($updateData, $where[1]);
        $this->pretreatment = $this->pdo->prepare($this->sql);
        $res = $this->pretreatment->execute($updateData);
        $this->trace($res, $startTime);
        return $res;
    }

    /**
     * 增加或减少给定字段的值
     *
     * @param  string $key   字段名称
     * @param  int    $value 递加值(正数递加，负数递减)
     */
    public function increment(string $key, int $value = 1)
    {
        $startTime = microtime(true);
        $value    = intval($value);
        $this->sql = "UPDATE {$this->table} SET {$key} = {$key} + {$value}";
        $where     = $this->getWhere();
        $this->sql .= $where[0] . ';';
        return $this->query($this->sql, $where[1]);
    }

    /**
     * 查询单条数据
     * 查询成功返回数组形式的数据，失败返回空（可以使用empty函数判断）
     *
     * @param  $fields 查询字段（可省略，默认 *）
     */
    public function first($fields = null)
    {
        $startTime           = microtime(true);
        $preArray            = $this->prepare($fields);
        $this->sql           = $preArray[0];
        $this->pretreatment  = $this->pdo->prepare($this->sql);
        $this->pretreatment->execute($preArray[1]);
        $res = $this->pretreatment->fetch(PDO::FETCH_OBJ);
        $this->trace($res, $startTime);
        return $res;
    }

    /**
     * 查询多条数据
     *
     * 查询成功返回数组形式的数据，失败返回空（可以使用empty函数判断）
     * 可以配合条件、排序、分页等方法进行查询
     *
     * @param  $fields 查询字段（可省略，默认 *）
     */
    public function get($fields = null)
    {
        $startTime = microtime(true);
        $preArray  = $this->prepare($fields, false);
        $this->sql = $preArray[0];
        if (is_null($this->eachPage)) {
            $this->sql .= $this->getLimit() . ';';
        } else {
            if (empty($this->totalRows)) {
                $mode = '/^SELECT .* FROM (.*)$/Uis';
                preg_match($mode, $this->sql, $arr_preg);
                $sql = 'SELECT count(*) AS total FROM ' . $arr_preg['1'];
                if (strpos($sql, 'GROUP BY ')) {
                    $sql = 'SELECT count(*) AS total FROM (' . $sql . ') AS witCountTable;';
                }
                $pretreatment = $this->pdo->prepare($sql);
                $pretreatment->execute($preArray[1]);
                $arrTotal = $pretreatment->fetch(PDO::FETCH_OBJ);
                $pager = new Paginate($arrTotal->total, $this->eachPage);
            } else {
                $pager = new Paginate($this->totalRows, $this->eachPage);
            }
            $this->sql .= $pager->limit . ';';
        }
        $this->pretreatment  = $this->pdo->prepare($this->sql);
        $this->pretreatment->execute($preArray[1]);
        $res = $this->pretreatment->fetchAll(PDO::FETCH_OBJ);
        $this->trace($res, $startTime);
        if (is_null($this->eachPage)) {
            return $res;
        } else {
            $this->eachPage = null;
            return array($res, $pager);
        }
    }

    // 预处理
    public function prepare($fields, $limit = true)
    {
        $exeArray = [];
        $join = $this->getJoin();
        if (!empty($join)) {
            is_null($fields) ? $sql = 'SELECT * FROM `' . $this->table . '` ' . $join . ' ' : $sql = 'SELECT ' . $fields . ' FROM `' . $this->table . '` ' . $join . ' ';
        } else {
            is_null($fields) ? $sql = 'SELECT * FROM `' . $this->table . '` ' : $sql = 'SELECT ' . $fields . ' FROM `' . $this->table . '` ';
        }
        $where = $this->getWhere();
        if (!is_null($where)) {
            $sql .= $where[0];
            $exeArray = $where[1];
        }
        $limit ? $sql .= $this->getGroup() . $this->getOrder() . $this->getLimit() . ';' : $sql .= $this->getGroup() . $this->getOrder();
        return array($sql, $exeArray);
    }

    /**
     * 设置条件
     *
     * @param  $where  条件（使用 ? 作为展位符）
     * @param  $array  条件占位符对应的数值（数组格式）
     */
    public function where($where, $array)
    {
        $this->where[0] = $where;
        is_array($array) ? $this->where[1] = $array : $this->where[1] = array($array);
        return $this;
    }

    // 获取条件
    public function getWhere()
    {
        if (empty($this->where)) {
            return null;
        }
        $return = array(' WHERE ' . $this->where[0] . ' ', $this->where[1]);
        $this->where = null;
        return $return;
    }

    // 设置 group by
    public function groupBy($group)
    {
        $this->groupBy = $group;
        return $this;
    }

    // 获取 group by
    public function getGroup()
    {
        if (empty($this->groupBy)) {
            return null;
        }
        $group = $this->groupBy;
        $this->groupBy = null;
        return ' GROUP BY ' . $group . ' ';
    }

    // 设置排序
    public function orderBy($order)
    {
        $this->orderBy = $order;
        return $this;
    }

    // 获取排序
    public function getOrder()
    {
        if (empty($this->orderBy)) {
            return null;
        }
        $return  = 'ORDER BY ' . $this->orderBy . ' ';
        $this->orderBy = null;
        return $return;
    }

    // 设置多表联合
    public function join($join_sql)
    {
        $this->join = $join_sql;
        return $this;
    }

    // 获取多表联合信息
    public function getJoin()
    {
        if (empty($this->join)) {
            return null;
        }
        $return = $this->join;
        $this->join = null;
        return $return;
    }

    /**
     * 查询数据截取
     *
     *
     */
    public function limit($start, $length)
    {
        $this->limit = array($start, $length);
        return $this;
    }

    // 设置 获取
    public function getLimit()
    {
        if (empty($this->limit)) {
            return null;
        }
        $return = ' limit ' . $this->limit[0] . ',' . $this->limit[1] . ' ';
        $this->limit = null;
        return $return;
    }

    // 设置 分页
    public function paginate($eachPage = 10, $totalRows = 0)
    {
        $this->eachPage  = $eachPage;
        $this->totalRows = $totalRows;
        return $this;
    }

    // 获取分页
    public function getSql()
    {
        return $this->sql;
    }

    // 获取错误信息
    public function error()
    {
        $error = is_null($this->pretreatment) ? $this->pdo_obj->errorInfo() : $this->pretreatment->errorInfo();
        if (isset($error[2])) {
            return $error[2];
        }
        return null;
    }

    // 获取影响的数据条目数
    public function rowCount()
    {
        if (empty($this->pretreatment)) {
            return null;
        }
        return $this->pretreatment->rowCount();
    }

    // 获取刚刚插入数据的主键值
    public function lastInsertId()
    {
        return $this->pdo->lastInsertId();
    }

    // 获取 pdo 对象
    public function getDb()
    {
        return $this->pdo;
    }

    // 获取数据总数
    public function count()
    {
        $this->sql = "SELECT count(*) AS total FROM $this->table ";
        $where = $this->getWhere();
        $this->sql .= $where[0] . ';';
        $this->query($this->sql, $where[1]);
        $return = $this->pretreatment->fetch();
        if (empty($return['total'])) {
            return 0;
        }
        return $return['total'];
    }

    // 获取某个字段数据最大值
    public function max($field)
    {
        $this->sql = "SELECT max($field) AS max FROM $this->table";
        $where = $this->getWhere();
        $this->sql .= $where[0] . ';';
        $this->query($this->sql, $where[1]);
        $return = $this->pretreatment->fetch();
        if (empty($return['max'])) {
            return 0;
        }
        return $return['max'];
    }

    // 获取某个字段数据最小值
    public function min($field)
    {
        $this->sql = "SELECT min($field) AS min FROM $this->table";
        $where = $this->getWhere();
        $this->sql .= $where[0] . ';';
        $this->query($this->sql, $where[1]);
        $retutn = $this->pretreatment->fetch();
        if (empty($retutn['min'])) {
            return 0;
        }
        return $retutn['min'];
    }

    // 获取某个字段数据平均值
    public function avg($field)
    {
        $this->sql = "SELECT avg($field) AS avg FROM $this->table";
        $where = $this->getWhere();
        $this->sql .= $where[0] . ';';
        $this->query($this->sql, $where[1]);
        $return = $this->pretreatment->fetch();
        if (empty($return['avg'])) {
            return 0;
        }
        return $return['avg'];
    }

    // 获取某个字段数据总和
    public function sum($field)
    {
        $this->sql = "SELECT sum($field) AS sum FROM $this->table";
        $where = $this->getWhere();
        $this->sql .= $where[0] . ';';
        $this->query($this->sql, $where[1]);
        $return = $this->pretreatment->fetch();
        if (empty($return['sum'])) {
            return 0;
        }
        return $return['sum'];
    }

    // 获取 mysql 版本
    public function mysqlVersion()
    {
        $this->query('SELECT version();');
        $return = $this->pretreatment->fetch();
        return $return[0];
    }

    // 获取当前数据表表结构
    public function structure()
    {
        $this->query('SELECT ORDINAL_POSITION, COLUMN_NAME, DATA_TYPE, COLUMN_COMMENT
                        FROM information_schema.columns
                        WHERE table_schema = ?
                        AND table_name = ?
                        ORDER BY ORDINAL_POSITION ASC;', [$this->conf['database'], $this->table]);
        return $this->queryFetchAll();
    }

    // 开启事务
    public function beginTransaction()
    {
        $this->pdo->beginTransaction();
    }

    // 提交事务
    public function commit()
    {
        $this->pdo->commit();
    }

    // 回滚
    public function rollback()
    {
        $this->pdo->rollback();
    }

    // 控制台打印刚刚执行的 sql 语句
    public function debugSql()
    {
        echo '<script>console.log("log - sql 命令 : ' . $this->sql . '");</script>';
    }
}
